﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using NPOI.HSSF.UserModel;

namespace GoldenGoalWeekly
{
    class Program
    {
        static void Main(string[] args)
        {


            string templatePath = string.Empty;
            string savePath = string.Empty;


#if DEBUG

            templatePath = @"D:\MyTemplatePath\GoldenGoalWeekly.xls";
            savePath = @"D:\MyTargetPath\GoldenGoalWeekly.xls";

#else
            if (args.Length != 2)
            {
                Console.Out.WriteLine("GoldenGoalWeekly.exe takes 2 parameter.");
                Console.Out.WriteLine("e.g. GoldenGoalWeekly [template Path] [out put path]");
                return;
            }
            else
            {
                templatePath = args[0];
                savePath = args[1];
            }
#endif


            FileStream fs = null;
            HSSFWorkbook wb = null;

            try
            {
                fs = new FileStream(templatePath, FileMode.Open, FileAccess.ReadWrite);
                wb = new HSSFWorkbook(fs);
                DataSet ds = ReadDataFromDB();

                HSSFCellStyle cellStyle = wb.CreateCellStyle() as HSSFCellStyle;
                HSSFDataFormat format = wb.CreateDataFormat() as HSSFDataFormat;
                cellStyle.DataFormat = format.GetFormat("yyyy-mm-dd");


                HSSFSheet sheetWeekly = wb.GetSheet("Weekly") as HSSFSheet;
                HSSFSheet sheetYTD = wb.GetSheet("YTD") as HSSFSheet;

                FillWeeklySheet(ds, sheetWeekly, cellStyle);

                FillYTDSheet(ds, sheetYTD, cellStyle);


                sheetWeekly.ForceFormulaRecalculation = true;
                sheetYTD.ForceFormulaRecalculation = true;

                fs.Close();
                fs.Dispose();

                fs = new FileStream(savePath, FileMode.Create, FileAccess.Write);
                wb.Write(fs);
            }
            catch (Exception ex)
            {
                Console.Out.WriteLine(ex.Message);
            }
            finally
            {
                if (fs != null)
                {
                    //fs.Flush();
                    fs.Close();
                    fs.Dispose();
                }
                wb.Dispose();
            }

        }

        private static void FillYTDSheet(DataSet ds, HSSFSheet sheetYTD, HSSFCellStyle cellStyle)
        {
            HSSFRow row = null;
            System.Data.DataTable dtYTD = ds.Tables["GoldenGoalWeeklyYTD"];

            for (int i = 0; i < dtYTD.Rows.Count; i++)
            {
                row = sheetYTD.CreateRow(i + 1) as HSSFRow;

                for (int j = 0; j < dtYTD.Columns.Count; j++)
                {
                    if (j == 0 || j == 1)
                    {
                        DateTime date = Convert.ToDateTime(dtYTD.Rows[i][j]);
                        HSSFCell cell = row.CreateCell(j) as HSSFCell;
                        cell.SetCellValue(date);
                        cell.CellStyle = cellStyle;
                        continue;
                    }
                    if (j == 2 || j == 4 || j == 6)
                    {
                        row.CreateCell(j).SetCellValue(Convert.ToInt64(dtYTD.Rows[i][j]));
                        continue;
                    }
                    if (j == 3 | j == 5)
                    {
                        row.CreateCell(j).SetCellValue(Convert.ToDouble(dtYTD.Rows[i][j]));
                        continue;
                    }
                }
            }
        }

        private static void FillWeeklySheet(DataSet ds, HSSFSheet sheetWeekly, HSSFCellStyle cellStyle)
        {
            HSSFRow row = null;
            System.Data.DataTable dtWeekly = ds.Tables["GoldenGoalWeekly"];


            for (int i = 0; i < dtWeekly.Rows.Count; i++)
            {
                row = sheetWeekly.CreateRow(i + 1) as HSSFRow;
                for (int j = 0; j < dtWeekly.Columns.Count; j++)
                {
                    if (j == 0 || j == 1)
                    {
                        DateTime date = Convert.ToDateTime(dtWeekly.Rows[i][j]);
                        HSSFCell cell = row.CreateCell(j) as HSSFCell;
                        cell.SetCellValue(date);
                        cell.CellStyle = cellStyle;
                        continue;
                    }
                    if (j == 3 || j == 5)
                    {
                        row.CreateCell(j).SetCellValue(Convert.ToDouble(dtWeekly.Rows[i][j]));
                        continue;
                    }
                    if (j == 2 || j == 4 || j == 6)
                    {
                        row.CreateCell(j).SetCellValue(Convert.ToInt64(dtWeekly.Rows[i][j]));
                        continue;
                    }
                }
            }
        }

        public static DataSet ReadDataFromDB()
        {
#if DEBUG
            string connStr = "Data Source=.;Integrated Security=SSPI;Initial Catalog = test";
#else
            string connStr = "Data Source=TK2MPBISQLREP1;Integrated Security=SSPI;Initial Catalog = Scratch";
#endif


            DataSet ds = new DataSet();

            SqlConnection conn = null;
            SqlDataAdapter adapter = null;
            SqlDataAdapter adapter2 = null;


            try
            {
                conn = new SqlConnection(connStr);
                conn.Open();

                SqlCommand cmdMonthly = new SqlCommand("GoldenGoalWeekly", conn);
                SqlCommand cmdYTD = new SqlCommand("GoldenGoalWeeklyYTD", conn);
                cmdMonthly.CommandType = CommandType.StoredProcedure;
                cmdYTD.CommandType = CommandType.StoredProcedure;

                adapter = new SqlDataAdapter(cmdMonthly);
                adapter2 = new SqlDataAdapter(cmdYTD);

                adapter.Fill(ds, "GoldenGoalWeekly");
                adapter2.Fill(ds, "GoldenGoalWeeklyYTD");
            }
            catch (Exception ex)
            {
                Console.Out.WriteLine(ex.Message);
            }
            finally
            {
                if (adapter != null)
                    adapter.Dispose();
                if (adapter2 != null)
                    adapter2.Dispose();
                if (conn != null)
                    conn.Dispose();
            }
            return ds;
        }
    }
}
